package org.barman.servlet;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.Date;

import javax.servlet.ServletException;
import javax.servlet.ServletOutputStream;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import com.itextpdf.*;
import com.itextpdf.text.Anchor;
import com.itextpdf.text.Anchor;
import com.itextpdf.text.BadElementException;
import com.itextpdf.text.BaseColor;
import com.itextpdf.text.Chapter;
import com.itextpdf.text.Document;
import com.itextpdf.text.DocumentException;
import com.itextpdf.text.Element;
import com.itextpdf.text.Font;
import com.itextpdf.text.List;
import com.itextpdf.text.ListItem;
import com.itextpdf.text.Paragraph;
import com.itextpdf.text.Phrase;
import com.itextpdf.text.Section;
import com.itextpdf.text.pdf.PdfPCell;
import com.itextpdf.text.pdf.PdfPTable;
import com.itextpdf.text.pdf.PdfWriter;

import org.barman.exception.PersistenceException;
import org.barman.util.DbHelper;

import sun.org.mozilla.javascript.internal.json.JsonParser.ParseException;

/**
 * Servlet implementation class Reprt_gen
 */
@WebServlet("/Reprt_gen")
public class Reprt_gen extends HttpServlet {
	private static final long serialVersionUID = 1L;

	/**
	 * @see HttpServlet#HttpServlet()
	 */
	public Reprt_gen() {
		super();
		// TODO Auto-generated constructor stub
	}

	/**
	 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse
	 *      response)
	 */
	protected void doGet(HttpServletRequest request,
			HttpServletResponse response) throws ServletException, IOException {
		// TODO Auto-generated method stub
	}

	/**
	 * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse
	 *      response)
	 */
	protected void doPost(HttpServletRequest request,
			HttpServletResponse response) throws ServletException, IOException {

		try {
			Connection connection = DbHelper.getConnection();
			String FILE = "D://Reports.pdf";
			Document document = new Document();
			PdfWriter.getInstance(document, new FileOutputStream(FILE));

			Font catFont = new Font(Font.FontFamily.TIMES_ROMAN, 18, Font.BOLD);

			Font redFont = new Font(Font.FontFamily.TIMES_ROMAN, 12,
					Font.NORMAL, BaseColor.RED);

			Font subFont = new Font(Font.FontFamily.TIMES_ROMAN, 16, Font.BOLD);

			Font smallBold = new Font(Font.FontFamily.TIMES_ROMAN, 12,
					Font.BOLD);

			String start_date = request.getParameter("start_date");
			String end_date = request.getParameter("end_date");
			String rep_type = request.getParameter("rep_gen");

			if ("".equals(start_date) || "".equals(end_date)
					|| "".equals(rep_type)) {
				response.sendRedirect("ErrorPage.jsp");
			}

			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm");
			java.util.Date _start_date = sdf.parse(start_date);
			java.util.Date _end_date = sdf.parse(end_date);

			long date_start = _start_date.getTime();
			long date_end = _end_date.getTime();

			Timestamp tmp_st_start = new Timestamp(date_start);
			Timestamp tmp_st_end = new Timestamp(date_end);

			if (rep_type.equals("items")) {
				document.open();
				document.add(new Paragraph("Item Report", catFont));
				document.add(new Paragraph(" "));
				document.add(new Paragraph(" "));
				document.add(new Paragraph("Created  " + new Date(), smallBold));
				document.add(new Paragraph(" "));
				document.add(new Paragraph(" "));
				document.add(new Paragraph(" "));

				PdfPTable table = new PdfPTable(3);

				PdfPCell c1 = new PdfPCell(new Phrase("Item name"));
				c1.setHorizontalAlignment(Element.ALIGN_CENTER);
				table.addCell(c1);

				c1 = new PdfPCell(new Phrase("Item price"));
				c1.setHorizontalAlignment(Element.ALIGN_CENTER);
				table.addCell(c1);

				c1 = new PdfPCell(new Phrase("Item create date"));
				c1.setHorizontalAlignment(Element.ALIGN_CENTER);
				table.addCell(c1);
				table.setHeaderRows(1);

				PreparedStatement preparestatement = connection
						.prepareStatement("Select * from item where  created >= ? and created <= ?");
				preparestatement.setTimestamp(1, tmp_st_start);
				preparestatement.setTimestamp(2, tmp_st_end);
				ResultSet rs = preparestatement.executeQuery();
				while (rs.next()) {
					double rs1= rs.getDouble(3);
					String Double1 = Double.toString(rs1);
					table.addCell(rs.getString(2));
					table.addCell(Double1);
					table.addCell(rs.getTimestamp(4).toString());
				}

				document.add(table);
				document.close();
				connection.close();
			}
			
			
			if (rep_type.equals("orders")) {
				document.open();
				document.add(new Paragraph("Order Report", catFont));
				document.add(new Paragraph(" "));
				document.add(new Paragraph(" "));
				document.add(new Paragraph("Created  " + new Date(), smallBold));
				document.add(new Paragraph(" "));
				document.add(new Paragraph(" "));
				document.add(new Paragraph(" "));

				PdfPTable table = new PdfPTable(5);

				PdfPCell c1 = new PdfPCell(new Phrase("Order ID"));
				c1.setHorizontalAlignment(Element.ALIGN_CENTER);
				table.addCell(c1);

				c1 = new PdfPCell(new Phrase("Order Status"));
				c1.setHorizontalAlignment(Element.ALIGN_CENTER);
				table.addCell(c1);

				c1 = new PdfPCell(new Phrase("Username"));
				c1.setHorizontalAlignment(Element.ALIGN_CENTER);
				table.addCell(c1);
				table.setHeaderRows(1);
				
				c1 = new PdfPCell(new Phrase("First name"));
				c1.setHorizontalAlignment(Element.ALIGN_CENTER);
				table.addCell(c1);
				table.setHeaderRows(1);
				
				c1 = new PdfPCell(new Phrase("Role"));
				c1.setHorizontalAlignment(Element.ALIGN_CENTER);
				table.addCell(c1);
				table.setHeaderRows(1);
				

				PreparedStatement preparestatement = connection
						.prepareStatement("Select order.id , order.status ,user.username,user.first_name, user.role from barman.order JOIN  barman.user  ON order.waiter_id = barman.user.id or order.barman_id=barman.user.id where order.created >= ? and order.created <= ?");
				preparestatement.setTimestamp(1, tmp_st_start);
				preparestatement.setTimestamp(2, tmp_st_end);
				ResultSet rs = preparestatement.executeQuery();
				while (rs.next()) {
					table.addCell(rs.getString(1));
					table.addCell(rs.getString(2));
					table.addCell(rs.getString(3));
					table.addCell(rs.getString(4));
					table.addCell(rs.getString(5));
				}

				document.add(table);
				document.close();
				connection.close();
			}
			
			

		
			

		} catch (java.text.ParseException e) {
			e.printStackTrace();
		} catch (PersistenceException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (DocumentException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		response.setContentType("application/octet-stream");
		response.setHeader("Content-Disposition",
		"attachment;filename=Reports.pdf");

		File file = new File("D://Reports.pdf");
		FileInputStream fileIn = new FileInputStream(file);
		ServletOutputStream out = response.getOutputStream();
		 
		byte[] outputByte = new byte[4096];
		//copy binary contect to output stream
		while(fileIn.read(outputByte, 0, 4096) != -1)
		{
			out.write(outputByte, 0, 4096);
		}
		fileIn.close();
		out.flush();
		out.close();

	}

}
